package dbconnection;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;


import loja.Armazem;
import loja.JaExisteException;
import loja.Produto;
import loja.Quebra;
import loja.Stock;

/**
 * @author IME 2010
 * 
 * Class used to establish a connection with a defined DataBase
 */

public class BDstock {


	private BaseDados base_dados = null;


	public BDstock(BaseDados bd){

		this.base_dados=bd;

	}

	/***********************************************************
	 * Inserts the Stock s into the DataBase
	 * @param s Stock to be inserted in the DataBase
	 * @throws JaExisteException if the Stock s already exists in the DataBase
	 * @return true if the Stock s was successfully inserted into the DataBase; false otherwise
	 ***********************************************************/
	protected boolean adiciona(Stock s) {
		if(s!=null&&s.getProduto()!=null&&s.getArmazem()!=null){
			String tablename="stock";
			String query = "insert INTO `" + tablename +"` (`produto_idproduto`, `armazem_idarmazem`, `quantidade`, `valor`, `min` ,`max`) VALUES ('"+s.getProduto().getCodigo()+"', '"+s.getArmazem().getId_armazem()+"', '"+s.getQuantidade()+"', '"+s.getValor()+"', '"+s.getMin()+"', '"+s.getMax()+"');" ;
			try {
				base_dados.executeStatement(query);
				return true;
			} catch (SQLException e) {
				return false;
			}
		}
		return false;
	}

	/**
	 * Updates the Stock stock in the DataBase to match the given fields
	 * @param stock Stock to be updated in the DataBase
	 * @param p	new Produto for the Stock s
	 * @param quantidade	new quantidade for the Stock s
	 * @param min new min for the Stock s
	 * @param max new max for the Stock s
	 * @param armazem new Armazem for the Stock s
	 * @return true if the Stock s was successfully updated; false otherwise
	 */
	protected boolean alteraStock(Stock stock, Produto p, int quantidade, int min, int max,Armazem armazem){
		boolean sucesso = false;
		if(stock!=null&&p!=null&&quantidade>=0&&armazem!= null&&min>=0&&max>=min){
			String tabela = "stock";
			String query = "UPDATE "+tabela+" SET `produto_idproduto`='"+p.getCodigo()+"', `armazem_idarmazem`='"+armazem.getId_armazem()
			+"', `max`='"+max+"', `quantidade`='"+quantidade+"', `min`='"+min+"' WHERE `idstock`='"+stock.getIDStock()+"';";


			try {
				base_dados.executeStatement(query);
				sucesso= true;
			} catch (SQLException e) {
				e.printStackTrace();
				sucesso=false;
			}
		}
		return sucesso;
	}
	
	/**
	 * Updates the Stock stock in the DataBase to match the given fields
	 * @param stock Stock to be updated in the DataBase
	 * @param p	new Produto for the Stock s
	 * @param quantidade	new quantidade for the Stock s
	 * @param min new min for the Stock s
	 * @param max new max for the Stock s
	 * @param armazem new Armazem for the Stock s
	 * @param valor new valor for the Stock s
	 * @return true if the Stock s was successfully updated; false otherwise
	 */
	protected boolean alteraStock(Stock stock, Produto p, int quantidade, int min, int max,Armazem armazem, int valor){
		boolean sucesso = false;
		if(stock!=null&&p!=null&&quantidade>=0&&armazem!= null&&min>=0&&max>=min){
			String tabela = "stock";
			String query = "UPDATE "+tabela+" SET `produto_idproduto`='"+p.getCodigo()+"', `armazem_idarmazem`='"+armazem.getId_armazem()
			+"', `max`='"+max+"', `quantidade`='"+quantidade+"', `min`='"+min+"', `valor`='"+valor+"' WHERE `idstock`='"+stock.getIDStock()+"';";


			try {
				base_dados.executeStatement(query);
				sucesso= true;
			} catch (SQLException e) {
				e.printStackTrace();
				sucesso=false;
			}
		}
		return sucesso;
	}

	/***********************************************************
	 * Indicates whether the Stock s exists in the DataBase
	 * @param s the Stock to be searched in the Database
	 * @return true if the Stock s exists in the DataBase; false otherwise
	 ***********************************************************/
	protected boolean existe(Stock s){
		if(s!=null){
			try{
				return existe(s.getIDStock());
			}catch(java.lang.NumberFormatException e){
				return false;
			}
		}
		return false;
	}

	/***********************************************************
	 * Removes the Stock s from the DataBase
	 * @param s the Stock to be removed from the Database
	 * @return true if the Stock s was correctly removed from the DataBase; false otherwise
	 ***********************************************************/
	protected boolean retira(Stock s)
	{
		boolean sucesso = false;
		if(s!=null&&existe(s)){


			String tablename="quebra";
			String query = "DELETE FROM " + tablename +" WHERE `stock_idstock` = '"+s.getIDStock()+"' ;";
			String tablename2="stock";
			String query2 = "DELETE FROM " + tablename2 +" WHERE `idstock` = '"+s.getIDStock()+"' ;";
			try {
				base_dados.executeStatement(query);
				base_dados.executeStatement(query2);
				sucesso= true;
			} catch (SQLException e) {
				e.printStackTrace();
				sucesso = false;
			}
		}
		return sucesso;
	}

	/***********************************************************
	 * Removes the Stock s from the DataBase
	 * @param p the Produto of all Stock to be removed from the Database
	 * @return true if the Stock s was correctly removed from the DataBase; false otherwise
	 ***********************************************************/
	protected boolean retira(Produto p)
	{
		boolean sucesso = false;
		if(p!=null&&existe(p)){

			String tablename="quebra";
			String query = "DELETE FROM " + tablename +" WHERE `stock_produto_idproduto` = '"+p.getCodigo()+"' ;";
			String tablename2="stock";
			String query2 = "DELETE FROM " + tablename2 +" WHERE `produto_idproduto` = '"+p.getCodigo()+"' ;";

			try {
				base_dados.executeStatement(query);
				base_dados.executeStatement(query2);
				sucesso= true;
			} catch (SQLException e) {
				e.printStackTrace();
				sucesso = false;
			}
		}
		return sucesso;
	}



	/***********************************************************
	 * Returns a Stock from the DataBase using the id argument as a reference
	 * @param id the integer which will be used to search a Stock by its id in the DataBase
	 * @return the Stock in the DataBase whose id is equal to the id argument; null if the id argument is not an id in the Table Stock in the DataBase;
	 ***********************************************************/
	protected Stock getStock(int id) {

		if(existe(id)){
			boolean excepcao_comunicacao = true;
			Stock s = null;
			while(excepcao_comunicacao){
				s = null;
			String tablename="stock";
			String query = "SELECT * FROM  "+tablename+"  WHERE `idstock` = '"+id+"' ;";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				rs.next();
				s=new Stock(null,rs.getInt(5),rs.getInt(7),rs.getInt(8),rs.getInt(6),null,rs.getInt(1));
				s.setArmazem(base_dados.getArmazem(rs.getInt(3)));
				s.setProduto(base_dados.getProduto(rs.getInt(2)));
				excepcao_comunicacao=false;
			} catch (SQLException e) {
				e.printStackTrace();
				excepcao_comunicacao=true;

			}
			}
			return s;
		}
		return null;
	}

	/***********************************************************
	 * Returns a Stock from the DataBase using the Quebra q argument as a reference
	 * @param q the Quebra which will be used to search a Stock in the DataBase
	 * @return the Stock in the DataBase to which the Quebra q is related; null if the Quebra q is in the DataBase
	 ***********************************************************/
	protected Stock getStock(Quebra q) {
		if(base_dados.existe(q)){
			boolean excepcao_comunicacao = true;
			Stock s = null;
			while(excepcao_comunicacao){
				s = null;
			String tablename="`stock`";
			String tablename2="`quebra`";
			String query = "SELECT * FROM  "+tablename+","+tablename2+"   WHERE `idquebra` = '"+q.getIdQuebra()+"' AND `idstock` = `stock_idstock`;";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				rs.next();
				s=new Stock(null,rs.getInt(5),rs.getInt(7),rs.getInt(8),rs.getInt(6),null,rs.getInt(1));
				s.setArmazem(base_dados.getArmazem(rs.getInt(3)));
				s.setProduto(base_dados.getProduto(rs.getInt(2)));
				excepcao_comunicacao=false;
			} catch (SQLException e) {
				e.printStackTrace();
				excepcao_comunicacao=true;
			}
			}
			return s;
		}
		return null;
	}
	
	/***********************************************************
	 * Returns a Stock from the DataBase using the Produto p and the Armazem a arguments as a reference
	 * @param p the Produto which will be used to search a Stock in the DataBase
	 * @param a the Armazem which will be used to search a Stock in the DataBase
	 * @return the Stock in the DataBase to which the Produto p and Armazem a are related; null if there are no Stock related with the Produto p and Armazem ani the DataBase
	 ***********************************************************/
	protected Stock getStock(Produto p, Armazem a) {
		if(base_dados.existe(p)&&base_dados.existe(a)){
			boolean excepcao_comunicacao = true;
			Stock s = null;
			while(excepcao_comunicacao){
				s = null;
			String tablename="`stock`";
			String coluna = "`produto_idproduto`";
			String coluna2 = "`armazem_idarmazem`";
			String query = "SELECT * FROM  "+tablename+"  WHERE "+coluna+" = '"+p.getCodigo()+"' AND "+coluna2+" = '"+a.getId_armazem()+"';";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				rs.next();
				s=new Stock(null,rs.getInt(5),rs.getInt(7),rs.getInt(8),rs.getInt(6),null,rs.getInt(1));
				s.setArmazem(base_dados.getArmazem(rs.getInt(3)));
				s.setProduto(base_dados.getProduto(rs.getInt(2)));
				excepcao_comunicacao=false;
			} catch (SQLException e) {
				e.printStackTrace();
				excepcao_comunicacao=true;
			}
			
			}
			return s;
		}
		return null;
	}
	/***********************************************************
	 * Returns all Stocks present in the DataBase
	 * @return the LinkedList<Stock> which contains all Stocks in the DataBase; null if the DataBase has no Stocks;
	 ***********************************************************/
	protected LinkedList<Stock> getStocks(){

		if(naoVazia()){	
			boolean excepcao_comunicacao = true;
			
			LinkedList<Stock> lista_stocks = null;
			while(excepcao_comunicacao){
				lista_stocks=null;
			String tabela="stock";
			String query = "SELECT * FROM " + tabela +";";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				lista_stocks = getStocks(rs);
				excepcao_comunicacao=false;
			} catch (SQLException e) {
				e.printStackTrace();
				excepcao_comunicacao = true;
			}
			}
			return lista_stocks;
		}
		return null;
	}

	/***********************************************************
	 * Returns all Stocks present in the DataBase whose name equal the nome argument
	 * @param nome the reference String with which to compare the name of each Stock in the DataBase
	 * @return the LinkedList<Stock> which contains all Stocks in the DataBase; null if the DataBase has no Stocks;
	 ***********************************************************/
	protected LinkedList<Stock> getStocks(Produto p){

		if(naoVazia() && p!=null){		
			LinkedList<Stock> lista_stocks = null;
			String tabela="stock";
			String coluna = "produto_idproduto";
			String query = "SELECT * FROM " + tabela +" WHERE `"+coluna+"`='"+p.getCodigo()+"';";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				lista_stocks = getStocks(rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}

			return lista_stocks;
		}
		return null;
	}

	/***********************************************************
	 * Returns all Stocks present in the DataBase whose name equal the nome argument
	 * @param nome the reference String with which to compare the name of each Stock in the DataBase
	 * @return the LinkedList<Stock> which contains all Stocks in the DataBase; null if the DataBase has no Stocks;
	 ***********************************************************/
	protected LinkedList<Stock> getStocks(Armazem a){

		if(naoVazia() && a!=null){		
			LinkedList<Stock> lista_stocks = null;
			String tabela="stock";
			String coluna = "armazem_idarmazem";
			String query = "SELECT * FROM " + tabela +" WHERE `"+coluna+"`='"+a.getId_armazem()+"';";
			try {
				ResultSet rs = base_dados.executeStatementQuery(query);
				lista_stocks = getStocks(rs);
			} catch (SQLException e) {
				e.printStackTrace();
			}
			return lista_stocks;
		}
		return null;
	}


	/***********************************************************
	 * Indicates whether an object with the given id exists under a table (given as an argument) in the DataBase
	 * @param id the reference id with which to search a table in the DataBase
	 * @param tablename the name of the table to search in the DataBase 
	 * @return true if the Object referenced by the id argument exists in the DataBase under the table with the name tablename argument
	 ***********************************************************/
	private boolean existe(int id) {
		String tablename = "stock";
		String column_name = "idstock";

		boolean existe = false;
		String query = "SELECT COUNT(*) FROM `" +tablename +"` WHERE `"+column_name+"` = '"+id+"' ;";
		try {
			ResultSet rs = base_dados.executeStatementQuery(query);
			rs.next();
			if(rs.getInt(1)>=1)
				existe = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return existe;
	}

	private boolean existe(Produto p) {
		String tablename = "stock";
		String column_name = "produto_idproduto";

		boolean existe = false;
		String query = "SELECT COUNT(*) FROM `" +tablename +"` WHERE `"+column_name+"` = '"+p.getCodigo()+"' ;";
		try {
			ResultSet rs = base_dados.executeStatementQuery(query);
			rs.next();
			if(rs.getInt(1)>=1)
				existe = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return existe;
	}

	private boolean naoVazia() {

		String tabela = "stock";
		boolean existe = false;
		String query = "SELECT COUNT(*) FROM `" + tabela +";";
		try {
			ResultSet rs = base_dados.executeStatementQuery(query);
			rs.next();
			if(rs.getInt(1)>=1)
				existe = true;
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return existe;
	}

	private LinkedList<Stock> getStocks(ResultSet rs) throws SQLException {

		LinkedList<Stock> lista_stocks= new LinkedList<Stock>();
		while (rs.next()){
//			lista_stocks.add(getStock(rs.getInt(1)));
//			rs.next();
			Stock s=new Stock(null,rs.getInt(5),rs.getInt(7),rs.getInt(8),rs.getInt(6),null,rs.getInt(1));
			s.setArmazem(base_dados.getArmazem(rs.getInt(3)));
			s.setProduto(base_dados.getProduto(rs.getInt(2)));
			lista_stocks.add(s);

		}

		return lista_stocks;
	}
}
